﻿using System;
using System.Data;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;

namespace GatewayInspect.Common
{
    public static class DataAccess
    {
        public static Database db = DatabaseFactory.CreateDatabase("connYG");

        public static DataSet ExecuteDataSet(string strSQL, params DbParameter[] parameters)
        {
            String sqlCheck = strSQL.ToUpper();
            if (sqlCheck.Contains("DELETE")
                || sqlCheck.Contains("UPDATE")
                || sqlCheck.Contains("DROP")
                || sqlCheck.Contains("ALTER")
                || sqlCheck.Contains("TRUNCATE"))
                return null;

            DbCommand cmd = db.GetSqlStringCommand(strSQL);
            if (parameters != null && parameters.Length > 0)
            {
                cmd.Parameters.AddRange(parameters);
            }
            return db.ExecuteDataSet(cmd);
        }

        public static DataTable ExecuteDataTable(string strSQL, params DbParameter[] parameters)
        {
            DataSet ds = ExecuteDataSet(strSQL, parameters);
            if (ds != null && ds.Tables.Count > 0)
            {
                return ds.Tables[0];
            }
            return null;
        }

        public static int ExecuteNonQuery(string strSQL, params DbParameter[] parameters)
        {
            DbCommand cmd = db.GetSqlStringCommand(strSQL);
            if (parameters != null && parameters.Length > 0)
            {
                cmd.Parameters.AddRange(parameters);
            }
            return db.ExecuteNonQuery(cmd);
        }

        public static object ExecuteScalar(string strSQL, params DbParameter[] parameters)
        {
            DbCommand cmd = db.GetSqlStringCommand(strSQL);
            if (parameters != null && parameters.Length > 0)
            {
                cmd.Parameters.AddRange(parameters);
            }
            return db.ExecuteScalar(cmd);
        }

        public static bool CheckLogin(string sUserName, string sPassWord, string sOrgCode, out string sRole, out string sXM, out string sOrgName, out int iType)
        {
            bool bResult = false;
            sRole = string.Empty;
            sXM = string.Empty;
            sOrgName = string.Empty;
            iType = 0;

            string strSQL = "SELECT USERNAME, XM, ROLE, u.OrgCode, o.OrgName, o.Type FROM T_SYS_USER u JOIN T_SYS_ORG o ON u.OrgCode = o.OrgCode " +
                "WHERE upper(USERNAME) = :USERNAME AND PASSWORD = :PASSWORD AND u.OrgCode = :OrgCode";
            DbCommand cmd = db.GetSqlStringCommand(strSQL);
            db.AddInParameter(cmd, ":USERNAME", DbType.String, sUserName);
            db.AddInParameter(cmd, ":PASSWORD", DbType.String, sPassWord);
            db.AddInParameter(cmd, ":OrgCode", DbType.String, sOrgCode);
            DataTable dt = db.ExecuteDataSet(cmd).Tables[0];

            if (dt.Rows.Count > 0)//登录成功
            {
                sRole = dt.Rows[0]["ROLE"] == null ? "" : dt.Rows[0]["ROLE"].ToString();
                sXM = dt.Rows[0]["XM"] == null ? "" : dt.Rows[0]["XM"].ToString();
                sOrgName = dt.Rows[0]["OrgName"] == null ? "" : dt.Rows[0]["OrgName"].ToString();
                iType = dt.Rows[0]["Type"] == null ? 0 : Convert.ToInt32(dt.Rows[0]["Type"]);
                bResult = true;
            }
            return bResult;
        }

        //超级管理员ADMIN 登录
        public static bool CheckLogin_Super(string sUserName, string sPassWord, string sOrgCode, out string sRole, out string sXM, out string sOrgName, out int iType)
        {
            bool bResult = false;
            sRole = string.Empty;
            sXM = string.Empty;
            sOrgName = string.Empty;
            iType = 0;

            if (sUserName == "ADMIN")
            {
                string strSQL = "SELECT XM, ROLE FROM T_SYS_USER WHERE upper(USERNAME) = 'ADMIN' AND PASSWORD = :PASSWORD";
                DbCommand cmd = db.GetSqlStringCommand(strSQL);
                db.AddInParameter(cmd, ":PASSWORD", DbType.String, sPassWord);
                DataTable dt = db.ExecuteDataSet(cmd).Tables[0];
                if (dt.Rows.Count > 0)//登录成功
                {
                    sRole = dt.Rows[0]["ROLE"] == null ? "" : dt.Rows[0]["ROLE"].ToString();
                    sXM = dt.Rows[0]["XM"] == null ? "" : dt.Rows[0]["XM"].ToString();
                    //sOrgCode = "01";

                    strSQL = "select * from T_SYS_ORG where ORGCODE = '01'";
                    dt = db.ExecuteDataSet(db.GetSqlStringCommand(strSQL)).Tables[0];
                    if (dt.Rows.Count > 0)
                        sOrgName = dt.Rows[0]["OrgName"] == null ? "" : dt.Rows[0]["OrgName"].ToString();

                    bResult = true;
                }
            }
            return bResult;
        }
        //安顺停车场管理员admin_as 登录
        public static bool CheckASLogin(string sUserName, string sPassWord, string sOrgCode, out string sRole, out string sXM, out string sOrgName, out int iType)
        {
            bool bResult = false;
            sRole = string.Empty;
            sXM = string.Empty;
            sOrgName = string.Empty;
            iType = 1;

            if (sUserName == "ADMIN_AS")
            {
                string strSQL = "SELECT XM, ROLE FROM T_SYS_USER WHERE upper(USERNAME) = 'ADMIN_AS' AND PASSWORD = :PASSWORD";
                DbCommand cmd = db.GetSqlStringCommand(strSQL);
                db.AddInParameter(cmd, ":PASSWORD", DbType.String, sPassWord);
                DataTable dt = db.ExecuteDataSet(cmd).Tables[0];
                if (dt.Rows.Count > 0)//登录成功
                {
                    sRole = dt.Rows[0]["ROLE"] == null ? "" : dt.Rows[0]["ROLE"].ToString();
                    sXM = dt.Rows[0]["XM"] == null ? "" : dt.Rows[0]["XM"].ToString();
                    string strSQLName = "SELECT ORGNAME FROM T_SYS_ORG WHERE ORGCODE='" + sOrgCode + "'";
                    DataTable name = DataAccess.ExecuteDataTable(strSQLName);
                    sOrgName = name.Rows[0]["ORGNAME"].ToString().Trim();
                    bResult = true;
                }
            }
            return bResult;
        }
        //平安停车场管理员admin_as 登录
        public static bool CheckPALogin(string sUserName, string sPassWord, string sOrgCode, out string sRole, out string sXM, out string sOrgName, out int iType)
        {
            bool bResult = false;
            sRole = string.Empty;
            sXM = string.Empty;
            sOrgName = string.Empty;
            iType = 1;

            if (sUserName == "ADMIN_PA")
            {
                string strSQL = "SELECT XM, ROLE FROM T_SYS_USER WHERE upper(USERNAME) = 'ADMIN_PA' AND PASSWORD = :PASSWORD";
                DbCommand cmd = db.GetSqlStringCommand(strSQL);
                db.AddInParameter(cmd, ":PASSWORD", DbType.String, sPassWord);
                DataTable dt = db.ExecuteDataSet(cmd).Tables[0];
                if (dt.Rows.Count > 0)//登录成功
                {
                    sRole = dt.Rows[0]["ROLE"] == null ? "" : dt.Rows[0]["ROLE"].ToString();
                    sXM = dt.Rows[0]["XM"] == null ? "" : dt.Rows[0]["XM"].ToString();
                    string strSQLName = "SELECT ORGNAME FROM T_SYS_ORG WHERE ORGCODE='" + sOrgCode + "'";
                    DataTable name = DataAccess.ExecuteDataTable(strSQLName);
                    sOrgName = name.Rows[0]["ORGNAME"].ToString().Trim();
                    bResult = true;
                }
            }
            return bResult;
        }
        //董家口停车场管理员admin_djk 登录
        public static bool CheckDJKLogin(string sUserName, string sPassWord, string sOrgCode, out string sRole, out string sXM, out string sOrgName, out int iType)
        {
            bool bResult = false;
            sRole = string.Empty;
            sXM = string.Empty;
            sOrgName = string.Empty;
            iType = 1;

            if (sUserName == "ADMIN_DJK")
            {
                string strSQL = "SELECT XM, ROLE FROM T_SYS_USER WHERE upper(USERNAME) = 'ADMIN_DJK' AND PASSWORD = :PASSWORD";
                DbCommand cmd = db.GetSqlStringCommand(strSQL);
                db.AddInParameter(cmd, ":PASSWORD", DbType.String, sPassWord);
                DataTable dt = db.ExecuteDataSet(cmd).Tables[0];
                if (dt.Rows.Count > 0)//登录成功
                {
                    sRole = dt.Rows[0]["ROLE"] == null ? "" : dt.Rows[0]["ROLE"].ToString();
                    sXM = dt.Rows[0]["XM"] == null ? "" : dt.Rows[0]["XM"].ToString();
                    string strSQLName = "SELECT ORGNAME FROM T_SYS_ORG WHERE ORGCODE='" + sOrgCode + "'";
                    DataTable name = DataAccess.ExecuteDataTable(strSQLName);
                    sOrgName = name.Rows[0]["ORGNAME"].ToString().Trim();
                    bResult = true;
                }
            }
            return bResult;
        }
    }
}
